--客户拓展表
with customer_addition as (
select
       code,
       name,
       customer_category,
case when customer_category=1 then '终端寄件'
     when customer_category=2 then '农特转递'
      when   customer_category=3 then '总部共享'
       when    customer_category=4 then '代理区共享'
       when    customer_category=5 then 'VIP'
       when    customer_category=6 then 'VIP菜鸟'
end as customer_category_name
from jms_dim.dim_lmdm_sys_customer_addition
--where customer_category is not null
),
 --网点信息维度表
network_expand as(
 select
      id,                            --网点ID
      code,                      --网点编码
      name,                      --网点名字
      subordinate_agent_name,    --所属代理区
      subordinate_agent_id,      --所属代理区ID
      subordinate_agent_code,    --所属代理区code
     first_franchisee_id    ,    -- 一级加盟商id',
     first_franchisee_code,     -- '一级加盟商code',
     first_franchisee_name    -- 一级加盟商',
 from jms_dim.dim_lmdm_sys_network_expand
 --group by code
 ),
customer_wide_detail as (
select
      customer_code,                                                              --客户编码
      max(customer_name) as customer_name,                                        --客户名字
      concat_ws(',',collect_set(ordersource_code)) as ordersource_code,           --平台ID  --指客户的来源，例如紫金山、逍遥峰、桃花岛、七星潭等 若一个客户有多个来源
      ordersource_name,                                                           --平台名字
      max(customer_type_id) as customer_type_id  ,                                -- 客户类型   --客户类型咋没有类型名字：客户类别是否是指“商务件、电商件、个人件”
      max(customer_type_name) as customer_type_name,                              --客户类型名字
      max(customer_attribute_id) as customer_attribute_id ,                       --客户属性ID
      max(customer_attribute_name) as customer_attribute_name,                    --客户属性名字   指“农特客户、专业市场客户、项目大客户、共享客户（寄件平台类）、共享客户（门店调拨类）、网点散客共用客户、自寄服务点客户”
      max(customer_belong_network_id) as customer_belong_network_id ,             -- 客户所属网点ID
      max(customer_belong_network_name) as  customer_belong_network_name ,        --  客户所属网点名字
      max(customer_province_id) as customer_province_id   ,                       -- 客户所属省份id
      max(customer_province_desc) as customer_province_desc ,                     --客户所属省份名字
      max(customer_city_id) as customer_city_id     ,                             -- 客户所属城市id
      max(customer_city_desc) as customer_city_desc,                              --  客户所属城市名字',
      max(customer_area_id) as customer_area_id   ,                               --  客户所属区域id
      max(customer_area_desc) as customer_area_desc ,                             -- 客户所属区县的名字',
      sum(day_customer_sum)  as day_customer_sum ,                                --当日客户运单量
      min(customer_signed_time) as customer_signed_time,                          --客户创建时间
      sum(day_customer_weight_sum) as   day_customer_weight_sum,                    --内部计费重量
      dt                                                                          --日期
from (
select
      customer_code,                                                              --客户编码
      max(customer_name) as customer_name,                                        --客户名字
      concat_ws(',',collect_set(ordersource_code)) as ordersource_code,           --平台ID  --指客户的来源，例如紫金山、逍遥峰、桃花岛、七星潭等 若一个客户有多个来源
      case when  ordersource_name='紫金山' then '紫金山'
           when  ordersource_name='逍遥峰' then '逍遥峰'
           when  ordersource_name='桃花岛' then '桃花岛'
           when  ordersource_name='七星潭' then '七星潭'
           when  ordersource_name='极地湾' then '极地湾'
           when  ordersource_name='有赞' then '有赞'
           when  ordersource_name='麦田圈' then '麦田圈'
           when  ordersource_name='苏宁' then '苏宁'
           else '其他' end as  ordersource_name,                                  --平台名字
      max(customer_type_id) as customer_type_id  ,                                -- 客户类型   --客户类型咋没有类型名字：客户类别是否是指“商务件、电商件、个人件”
      max(customer_type_name) as customer_type_name,                              --客户类型名字
      max(customer_attribute_id) as customer_attribute_id ,                        --客户属性ID
      max(customer_attribute_name) as customer_attribute_name,                    --客户属性名字   指“农特客户、专业市场客户、项目大客户、共享客户（寄件平台类）、共享客户（门店调拨类）、网点散客共用客户、自寄服务点客户”
      max(customer_belong_network_id) as customer_belong_network_id ,             -- 客户所属网点ID
      max(customer_belong_network_name) as  customer_belong_network_name ,        --  客户所属网点名字
      max(customer_province_id) as customer_province_id   ,                       -- 客户所属省份id
      max(customer_province_desc) as customer_province_desc ,                     --客户所属省份名字
      max(customer_city_id) as customer_city_id     ,                             -- 客户所属城市id
      max(customer_city_desc) as customer_city_desc,                              --  客户所属城市名字',
      max(customer_area_id) as customer_area_id   ,                               --  客户所属区域id
      max(customer_area_desc) as customer_area_desc ,                             -- 客户所属区县的名字',
      count(1)  as day_customer_sum ,                                             --当日客户运单量
      min(customer_signed_time) as customer_signed_time,                          --客户创建时间
      sum(package_charge_weight) as   day_customer_weight_sum,                    --内部计费重量
      dt                                                                          --日期
from jms_dwd.dwd_wide_customer_detail_incre_dt
where dt='{{ execution_date | cst_ds }}'
and nvl(is_void, 0) = 0
and customer_name not like 'BEST%'
and customer_belong_network_id not in ('2350','72','17063','70','22')
group by dt,customer_code,`ordersource_name`
)temp
group by dt,customer_code,`ordersource_name`
)

insert overwrite table jms_dm.dm_customer_base_detail_dt partition(dt)
  select
      b.customer_code,                                                            --客户编码
      b.customer_name,                                                            --客户名字
      b.ordersource_code,                                                                 --平台ID  --指客户的来源，例如紫金山、逍遥峰、桃花岛、七星潭等
      b.ordersource_name,                                                                 --平台名字
      b.customer_type_id  ,                                                               -- 客户类型   客户类别是否是指“商务件、电商件、个人件”
      b.customer_type_name,                                                              --客户类型名字
      d.customer_category,                                                               --客户类别  指“终端寄件、农特专递、总部共享、代理区共享、VIP、VIP菜鸟”
      d.customer_category_name,
      b.customer_attribute_id ,                                                         --客户属性ID
      b.customer_attribute_name,                                                        --客户属性名字   指“农特客户、专业市场客户、项目大客户、共享客户（寄件平台类）、共享客户（门店调拨类）、网点散客共用客户、自寄服务点客户”
      c.subordinate_agent_name,                                                         --所属代理区
      c.subordinate_agent_id,                                                           --所属代理区ID
      c.subordinate_agent_code,                                                         --所属代理区code
      c.first_franchisee_id    ,                                                        -- 一级加盟商id',
      c.first_franchisee_code,                                                          -- '一级加盟商code',
      c.first_franchisee_name  ,                                                        -- 一级加盟商',
      b.customer_belong_network_id ,                                                    -- 客户所属网点ID
      b.customer_belong_network_name ,                                                  --  客户所属网点名字
      b.customer_province_id   ,                                                        -- 客户所属省份id
      b.customer_province_desc ,                                                        --客户所属省份名字
      b.customer_city_id     ,                                                          -- 客户所属城市id
      b.customer_city_desc,                                                             --  客户所属城市名字',
      b.customer_area_id   ,                                                            --  客户所属区域id
      b.customer_area_desc ,                                                            -- 客户所属区县的名字',
      nvl(b.day_customer_sum,0) as day_customer_sum ,                                   --当日客户运单总量
      to_date(b.customer_signed_time) as customer_signed_time,                          --客户创建时间
      nvl(b.day_customer_weight_sum,0) as day_customer_weight_sum,                      --内部计费重量
      b.dt
  from  customer_wide_detail b
  left join network_expand c
  on b.customer_belong_network_id=c.id
  left join  customer_addition  d
  on  b.customer_code=d.code
  distribute by 10;
